package com.mp.approvalreq.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mp.approvalreq.service.RequestServicePrx;
import com.mp.comment.entity.Comment;
import com.mp.approvalreq.entity.*;
import com.mp.approvalreq.entity.dto.ComponentGroupDTO;
import com.mp.approvalreq.entity.po.ComponentGroup;
import com.mp.approvalreq.entity.vo.Component;
import com.mp.approvalreq.service.ReadTemplateServicePrx;
import com.mp.approvalreq.service.SelectApprovalServicePrx;
import com.mp.user.entity.User;
import com.mp.user.service.UserServicePrx;
import com.plusmoney.exception.RpcServerLogicalException;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.HTTPUtils;
import com.plusmoney.util.Utils;
import com.qq.tars.client.util.ServantUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.NDC;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.WorkbookUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static java.util.Comparator.*;

/**
 * 导出我申请的审批
 */
public class ExportMyApply {
    private static final Logger logger = LoggerFactory.getLogger(ExportMyApply.class);

    private final ExcelParam excelParam;

    public ExportMyApply(ExcelParam excelParam) {
        this.excelParam = excelParam;
    }

    // 标题行样式、月份行样式、左对齐样式、居中样式
    private CellStyle headStyle;
    private CellStyle leftDataStyle;
    private CellStyle centerDataStyle;

    private void initCellStyle(HSSFWorkbook wb) {
        headStyle = getHeadStyle(wb);
        leftDataStyle = getLeftCenterStyle(wb);
        centerDataStyle = getCenterAlignStyle(wb);
    }


    private SimpleDateFormat hmSDF;
    private SimpleDateFormat chineseHMSDF;
    private SimpleDateFormat ymdSDF;
    private SimpleDateFormat ymdCHSDF;

    private void initSimpleDateFormat() {
        hmSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        chineseHMSDF = new SimpleDateFormat("yyyy-MM-dd E HH:mm", Locale.CHINESE);
        ymdSDF = new SimpleDateFormat("yyyy-MM-dd");
        ymdCHSDF = new SimpleDateFormat("yyyy年M月d日");
    }


    // 组件Num,组件type
    private List<Integer> numList;
    private List<Integer> typeList;


    // 获取标题行样式
    private static CellStyle getHeadStyle(HSSFWorkbook wb) {
        Font headFont = ExcelUtil.createFont(wb, "微软雅黑", 16, false);
        CellStyle headRowStyle = ExcelUtil.createCellStyle(wb, 1, 1, 1, 1);
        headRowStyle.setFont(headFont);
        return headRowStyle;
    }

    // 获取单元格内容左对齐样式
    private static CellStyle getLeftCenterStyle(HSSFWorkbook wb) {
        Font itemFont = ExcelUtil.createFont(wb, "微软雅黑", 14, false);
        CellStyle leftDataStyle = ExcelUtil.createCommentLEFTStyle(wb, 1, 1, 1, 1);
        leftDataStyle.setFont(itemFont);

        return leftDataStyle;
    }

    // 获取单元格内容居中对齐样式
    private CellStyle getCenterAlignStyle(HSSFWorkbook wb) {
        Font itemFont = ExcelUtil.createFont(wb, "微软雅黑", 14, false);
        CellStyle centerDataRowStyle = ExcelUtil.createCellStyle(wb, 1, 1, 1, 1);
        centerDataRowStyle.setFont(itemFont);
        return centerDataRowStyle;
    }

    private void createExcelHeadByDay(HSSFSheet sheet, String templateName) throws Throwable {
        User user = excelParam.getUser();
        String sheetTime = getExportScopeStrByDays(excelParam.getDays());
        String exportTime = excelParam.getExportTime();
        try {
            int rowIndex = 1;
            Font titleFont = ExcelUtil.createFont(sheet.getWorkbook(), "微软雅黑", 26, false);
            CellStyle titleStyle = ExcelUtil.createCellStyle(sheet.getWorkbook(), 1, 1, 1, 1);
            titleStyle.setAlignment(HorizontalAlignment.LEFT);                                    // 左对齐
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);                            // 垂直居中
            titleStyle.setFont(titleFont);
            Row headerRow = sheet.createRow(rowIndex++);
            ExcelUtil.setHeight(headerRow, 3400);                                                // 固定标题行高3400
            ExcelUtil.createCommonCell(headerRow, 1, titleStyle, "大管加审批记录导出");                // 创建单元格参数：行,单元格所在列,该行样式,单元格内容      固定内容：大管加审批记录导出

            Font headerFont = ExcelUtil.createFont(sheet.getWorkbook(), "微软雅黑", 16, false);
            CellStyle headerStyle = ExcelUtil.createCellStyle(sheet.getWorkbook(), 1, 1, 1, 1, HorizontalAlignment.LEFT,
                    VerticalAlignment.CENTER);
            headerStyle.setFont(headerFont);

            Row exportCompanyName = sheet.createRow(rowIndex++);
            ExcelUtil.setHeight(exportCompanyName, 2700);
            ExcelUtil.createCommonCell(exportCompanyName, 1, headerStyle, "公司名 :" + user.getCompanyName());

            Row exportApprovalType = sheet.createRow(rowIndex++);
            ExcelUtil.setHeight(exportApprovalType, 2700);
            ExcelUtil.createCommonCell(exportApprovalType, 1, headerStyle, "审批类型 :" + templateName);

            Row exportScopeRow = sheet.createRow(rowIndex++);
            ExcelUtil.setHeight(exportScopeRow, 2700);
            String exportScopeStr = "审批时间 :" + sheetTime;
            ExcelUtil.createCommonCell(exportScopeRow, 1, headerStyle, exportScopeStr);

            Row exportTimeRow = sheet.createRow(rowIndex++);
            ExcelUtil.setHeight(exportTimeRow, 2700);
            ExcelUtil.createCommonCell(exportTimeRow, 1, headerStyle, "导出时间 :" + exportTime);

            Row exportContactRow = sheet.createRow(rowIndex);
            ExcelUtil.setHeight(exportContactRow, 2700);
            ExcelUtil.createCommonCell(exportContactRow, 1, headerStyle, "导出人/申请人 :" + user.getContactName());
        } catch (Throwable e) {
            logger.error(e.getMessage(), e);
            throw e;
        }
    }

    private void createExcelBodyByDays(HSSFSheet sheet, List<Request> reqList, int startIndex, Template template)
            throws Throwable {
        // 标题行内容: 编号、申请时间、模板组件、参与人、审批人、默认抄送人、临时抄送人、归档人、批准时间

        int startLineIndex = 1;                     // 开始列

        Row itemRow = ExcelUtil.createRow(sheet, startIndex);

        // 编号(审批单号)
        ExcelUtil.createCommonCell(itemRow, startLineIndex, headStyle, "编号");
        sheet.setColumnWidth(startLineIndex, 35 * 256);

        // 申请时间
        ExcelUtil.createCommonCell(itemRow, startLineIndex + 1, headStyle, "申请时间");
        sheet.setColumnWidth(startLineIndex + 1, 30 * 256);

        // 模板组件
        List<TemplateComponent> comList = getComponentList(template);
        shiftTemplateComponents(comList);                                   // 排除一些不需要的组件
        createComponentInSheet(comList, startLineIndex, itemRow);    // 打印组件

        if (template.getDefaultType() == Template.LEAVE) {
            leaveDurationIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "请假时长").getColumnIndex();
        }
        // 相关人群、审批数据
        relatePeopleIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "参与人").getColumnIndex();
        statusIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批状态").getColumnIndex();
        approverColIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批人").getColumnIndex();
        ccColIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "默认抄送人").getColumnIndex();
        ccTempColIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "临时抄送人").getColumnIndex();
        archiveIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "归档人").getColumnIndex();
        approvalTimeIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批时间").getColumnIndex();
        commentIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批评论").getColumnIndex();

        if (ListHelper.isNotNullEmpty(reqList)) {
            // 打印审批组件内容
            printRequestContent(sheet, reqList, startIndex);
        }

    }


    /**
     * 过滤掉重复的名字
     *
     * @param sheetNameList
     * @param name
     * @return
     */
    private static String shiftRepeatSheetName(List<String> sheetNameList, String name) {
        String sheetName;
        int count = 0;
        for (String previousName : sheetNameList) {
            if (previousName.equals(name)) {
                count++;
            }
        }
        sheetNameList.add(name);
        if (count == 0) {
            sheetName = name;
        } else {
            sheetName = name + "-" + (count + 1);
        }

        return sheetName;
    }


    /**
     * 用于合并
     */
    private List<LinkedList<Integer>> lineGroupIndexList = new LinkedList<>();


    private void recordNewExpenseVersionMap(List<Request> requests) {
        for (Request req : requests) {
            // 记录新版报销审批,需要合并
            if (req.getDefaultType() == Template.EXPENSES && req.getTotalMoney() != null) {
                newExpenseVersionMap.put(req.getRequestDkey(), req);
            }
        }
    }


    public void createExcel() throws Throwable {
        NDC.push("currentTimeMillis=" + System.currentTimeMillis());

        if (!excelParam.isExportMyApply()) {
            throw new RpcServerLogicalException("导出类型错误");
        }
        ReadTemplateServicePrx readTemplateService = excelParam.getReadService();
        RequestServicePrx requestServicePrx = ServantUtils.getObject(RequestServicePrx.class);
        User user = excelParam.getUser();
        List<User> userList = new ArrayList<>();
        userList.add(user);
        List<Integer> templateIds = excelParam.getTemplateIds();
        List<String> days = excelParam.getDays();

        //  获取所有需要导出的 模板的详情
        List<Template> templateList = readTemplateService.getTemplateDetailByTemplateIdList(templateIds);

        //  得到该公司所有员工contactId:User(contactID,name,离职时间)
        UserServicePrx userServicePrx = ServantUtils.getObject(UserServicePrx.class);
        contactMap = convertUserToPeople(userServicePrx.getAllUserBaseInfoList(user.getCompanyId()));

        //  导出审批类型：请假、外出、申购等等
        StringBuilder templateNames = new StringBuilder();

        HSSFWorkbook wb = new HSSFWorkbook();
        initCellStyle(wb);
        initSimpleDateFormat();

        List<String> sheetNameList = new LinkedList<>();

        for (Template template : templateList) {
            String sheetName = template.getTemplateName();
            templateNames.append(sheetName).append("、");
            sheetName = CheckUtil.replaceNonlicetSheetNameCharacter(sheetName);
            sheetName = shiftRepeatSheetName(sheetNameList, sheetName);

            HSSFSheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(sheetName));
            setNoGridLines(sheet);
            createExcelHeadByDay(sheet, sheetName);

            int lastHeaderRowIndex = sheet.getLastRowNum();           // 每个sheet的头部的最后一行(6)
            int itemStartIndex = lastHeaderRowIndex + 2;

            // 获取该用户在这些天申请过的审批模板
            List<Template> hasUsedTemplates = readTemplateService.getUsedTemplateInDaysByUser(template, days, userList, 1);
            if (ListHelper.isNullEmpty(hasUsedTemplates)) {
                createExcelBodyByDays(sheet, null, itemStartIndex, template);
            } else {
                for (Template usedTemp : hasUsedTemplates) {
                    List<Request> requests = requestServicePrx.getUserTemplateRequestListByDateScope(user.getCompanyInfoId(), usedTemp.getTemplateId(), days.get(0), days.get(1));

                    if (StringUtils.isEmpty(usedTemp.getComponentGroupJson())) {
                        recordNewExpenseVersionMap(requests);

                        try {
                            // 每个模板的打印不去影响其他打印
                            createExcelBodyByDays(sheet, requests, itemStartIndex, usedTemp);
                        } catch (Throwable e) {
                            logger.error(e.getMessage(), e);
                            MailUtils.sendMail(MailUtils.EMAIL, e.getMessage(), String.valueOf(usedTemp.getTemplateId()));
                        }
                    } else {
                        // 组件集
                        try {
                            SelectApprovalServicePrx selectApprovalService = ServantUtils.getObject(SelectApprovalServicePrx.class);
                            String componentGroupIds = ApprovalUtils.getComponentGroupIds(usedTemp.getComponentGroupJson());
                            List<ComponentGroupDTO> groupDTOs = selectApprovalService.getComponentGroupInfoDTOs(usedTemp.getCompanyId(), componentGroupIds);

                            List<ComponentGroup> groups = com.plusmoney.util.BeanUtils.dtos2pos(groupDTOs, ComponentGroup.class);
                            ApprovalUtils.setComponentGroupNum(usedTemp.getComponentGroupJson(), groups);

                            createExcelGroupBodyByDays(sheet, requests, itemStartIndex, usedTemp, groups);
                        } catch (Throwable e) {
                            logger.error(e.getMessage(), e);
                            MailUtils.sendMail(MailUtils.EMAIL, e.getMessage(), String.valueOf(usedTemp.getTemplateId()));
                            lineGroupIndexList.clear();
                        }
                    }

                    itemStartIndex = sheet.getLastRowNum() + 5;
                }
            }
            // 合并头部
            mergeHeaderRegion(sheet, lastHeaderRowIndex);
            // 调整单元格内容宽度
            autoSizeOfSheetCell(sheet);
        }

        if (templateNames.toString().endsWith("、")) {
            templateNames.deleteCharAt(templateNames.length() - 1);
        }

        List<File> fileList = getFileList(wb, templateNames);
        String emailContent = getEmailContentByDays(templateNames.toString());
        sendEmail(emailContent, fileList);

        NDC.pop();
    }

    /**
     * 每个组件集的开始位置
     */
    private Map<Integer, Integer> groupIndexMap = new HashMap<>();

    /**
     * 组件集里面也要排除掉无需打印的组件
     *
     * @param groups
     */
    private void shiftComponentGroup(List<ComponentGroup> groups) {
        for (ComponentGroup group : groups) {
            List<Component> components = new ArrayList<>();

            for (Component component : group.getComponents()) {
                if (component.getType() > 7 && component.getType() < 12) {
                    continue;
                }
                components.add(component);
            }

            components.sort(comparingInt(Component::getNum));
            group.setComponents(components);
        }

    }

    private void createExcelGroupBodyByDays(HSSFSheet sheet, List<Request> requests, int startIndex, Template template, List<ComponentGroup> groups) throws Throwable {
        // 每次打印完一种sheet表后都要清空
        lineGroupIndexList.clear();
        groupIndexMap.clear();

        // 标题行内容: 申请时间、申请人、模板组件、参与人、审批人、默认抄送人、临时抄送人、归档人、批准时间

        int itemIndex = startIndex;                 // 开始行
        int startLineIndex = 1;                     // 开始列
        int lastLineIndex = 0;                      // 最后列

        // 排除掉组件集里面无需打印的子组件
        shiftComponentGroup(groups);
        logger.info("该模板的groups:{}", groups.toString());

        for (int i = itemIndex; i <= itemIndex + 1; i++) {
            Row itemRow = ExcelUtil.createRow(sheet, i);
            if (i == itemIndex) {
                itemRow.setHeight((short) (69 * 20));
            }

            ExcelUtil.createCommonCell(itemRow, startLineIndex, headStyle, "编号");
            sheet.setColumnWidth(startLineIndex, 35 * 256);

            // 申请时间
            ExcelUtil.createCommonCell(itemRow, startLineIndex + 1, headStyle, "申请时间");
            sheet.setColumnWidth(startLineIndex + 1, 30 * 256);

            // 模板组件
            List<TemplateComponent> comList = getComponentList(template);
            // 排除一些不需要的组件
            shiftTemplateComponents(comList);

            if (i == itemIndex) {
                // 打印组件
                printGroupCom(comList, startLineIndex, itemRow, groups);
            } else {
                printSubComponent(comList, startLineIndex, itemRow, groups);
            }

            // 相关人群、审批数据
            relatePeopleIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "参与人").getColumnIndex();
            statusIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批状态").getColumnIndex();
            approverColIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批人").getColumnIndex();
            ccColIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "默认抄送人").getColumnIndex();
            ccTempColIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "临时抄送人").getColumnIndex();
            archiveIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "归档人").getColumnIndex();
            approvalTimeIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批时间").getColumnIndex();
            commentIndex = ExcelUtil.createCommonCell(itemRow, itemRow.getLastCellNum(), headStyle, "审批评论").getColumnIndex();
            lastLineIndex = itemRow.getLastCellNum();
        }

		/*
		   先合模板第一行中重复打印的组件集名称
		 */
        for (LinkedList<Integer> groupColumnIndex : lineGroupIndexList) {
            if (groupColumnIndex.size() < 2) {
                continue;
            }
            logger.info("合并开始列:{}, 合并结束列:{}", groupColumnIndex.getFirst(), groupColumnIndex.getLast());
            ExcelUtil.createRegion(sheet, itemIndex, itemIndex, groupColumnIndex.getFirst(), groupColumnIndex.getLast());
        }

        logger.info("第一列:{}, 最后一列:{}", startLineIndex, lastLineIndex);
        /*
         *  再合并模板第一行和第二行除组件集的以外的列
         */
        for (int i = startLineIndex; i < lastLineIndex; i++) {
            boolean isMerge = true;
            for (LinkedList<Integer> groupColumnIndex : lineGroupIndexList) {
                if (groupColumnIndex.contains(i)) {
                    logger.info("该{}不需要合并", i);
                    isMerge = false;
                    break;
                }
            }
            // 上下合并
            if (isMerge) {
                logger.info("开始行:{}, 结束行:{}, 开始列:{}, 结束列:{}", itemIndex, itemIndex + 1, i, i);
                ExcelUtil.createRegion(sheet, itemIndex, itemIndex + 1, i, i);
            }
        }

        itemIndex += 1;
        if (ListHelper.isNotNullEmpty(requests)) {
//			 打印审批组件内容
            printComponentGroupValue(sheet, requests, itemIndex + 1, groups);
        }

    }

    private void printComponentGroupValue(HSSFSheet sheet, List<Request> reqList, int headRowIndex, List<ComponentGroup> groups) throws Throwable {
        for (Request req : reqList) {
            headRowIndex += printRepeatGroupValue(sheet, req, headRowIndex, groups);
        }
    }

    private int getSortCount(int sortCount, List<TemplateComponent> componentList) {
        for (TemplateComponent component : componentList) {
            if (component.getType() == ContentType.COMPONENT_GROUP) {
                JSONArray arr = JSON.parseArray(component.getValue());
                if (Utils.isNotNull(arr) && arr.size() > sortCount) {
                    sortCount = arr.size();
                }
            }
        }
        return sortCount;
    }

    static class SortValue {
        Integer sort;
        List<Component> components;

        Integer getSort() {
            return sort;
        }

        void setSort(Integer sort) {
            this.sort = sort;
        }

        List<Component> getComponents() {
            return components;
        }

        void setComponents(List<Component> components) {
            this.components = components;
        }

        @Override
        public String toString() {
            return "SortValue{" +
                    "sort=" + sort +
                    ", components=" + components +
                    '}';
        }
    }

    /**
     * 组件集num, 组件集的值
     */
    private Map<Integer, List<SortValue>> sortMap = new HashMap<>();

    private void initSortMap(List<ComponentGroup> groups, List<TemplateComponent> componentList) {
        for (TemplateComponent component : componentList) {
            if (component.getType() == ContentType.COMPONENT_GROUP) {
                for (ComponentGroup group : groups) {
                    if (Objects.equals(component.getNum(), group.getNum())) {
						/*
						  组件集乜有填写时,前端不会传值, 这里要初始化一下
						 */
                        if (StringUtils.isEmpty(component.getValue())) {
                            List<SortValue> values = new ArrayList<>();
                            SortValue sortValue = new SortValue();
                            sortValue.setSort(1);
                            List<Component> components = new ArrayList<>();
                            for (Component component1 : group.getComponents()) {
                                components.add(initComponent(component1, ""));
                            }
                            sortValue.setComponents(components);
                            values.add(sortValue);
                            sortMap.put(component.getNum(), values);
                        } else {
                            sortMap.put(component.getNum(), setValue(component.getValue(), group.getComponents(), component.getNum()));
                        }
                    }
                }
            }
        }
    }


    private List<SortValue> setValue(String jsonValue, List<Component> components, int num) {
        List<SortValue> values = new ArrayList<>();

        JSONArray array = JSONArray.parseArray(jsonValue);
        for (int i = 0; i < array.size(); i++) {
            JSONObject object = array.getJSONObject(i);
            JSONArray valueArray = object.getJSONArray("value");
            Integer sort = object.getInteger("sort");

            logger.info("sort:{}, value:{}", sort, valueArray.toString());

			/*
			 [{
			 "sort":1,
			 "value": [{"num":1, "value":"xx"},{"num":2, "value":""},{"num":3, "value":""}]
			 },
			 {
			 "sort":2,
			 "value": [{"num":1, "value":"xx"},{"num":2, "value":""},{"num":3, "value":""}]
			 },
			 {
			 "sort":3,
			 "value": [{"num":1, "value":"xx"},{"num":2, "value":"xx"},{"num":3, "value":"xx"}]
			 }]
			 */
            List<Component> list = new ArrayList<>(components.size());
            for (Component component : components) {
                for (int j = 0; j < valueArray.size(); j++) {
                    JSONObject valueObject = valueArray.getJSONObject(j);
                    if (valueObject.getInteger("num").equals(component.getNum())) {
                        list.add(initComponent(component, valueObject.getString("value")));
                    }
                }
            }
            SortValue sortValue = new SortValue();
            sortValue.setSort(sort);
            sortValue.setComponents(list);

            values.add(sortValue);
        }

        logger.info("num:{} || SortValue:{}", num, values);

        return values;
    }


    private Component initComponent(Component component, String value) {
        Component com = new Component();

        if (StringUtils.isEmpty(value)) {
            com.setValue(value);
        } else {
            if (component.getType() == ContentType.GROUP_LOCATION) {
                JSONObject jsonObject = JSON.parseObject(value);
                String location = jsonObject.getString("location");
                location = location == null ? "" : location;
                com.setValue(location);
            } else {
                com.setValue(value);
            }
        }

        com.setName(component.getName());
        com.setNeed(component.getNeed());
        com.setNum(component.getNum());
        com.setType(component.getType());

        return com;
    }

    private SortValue initEmptyValue(SortValue sortValue) {
        SortValue initValue = new SortValue();
        initValue.setSort(sortValue.getSort());

        List<Component> components = new ArrayList<>();
        for (Component component : sortValue.getComponents()) {
            Component com = new Component();

            com.setValue(component.getValue());
            com.setName(component.getName());
            com.setNeed(component.getNeed());
            com.setNum(component.getNum());
            com.setType(component.getType());

            if (StringUtils.isNotEmpty(com.getValue())) {
                com.setValue("/");
            }

            components.add(com);
        }

        initValue.setComponents(components);
        return initValue;
    }

    /**
     * 记录已打印的组件集num
     */
    private int printRepeatGroupValue(HSSFSheet sheet, Request request, int headRowIndex, List<ComponentGroup> groups) throws Throwable {

        // 组件集中提交最多的次数
        int sortCount = 1;
        List<TemplateComponent> componentList = JSONObject.parseArray(request.getTemplateComponent(), TemplateComponent.class);
        sortCount = getSortCount(sortCount, componentList);
        logger.info("组件集中提交最多的组件集次数:{}", sortCount);

        initSortMap(groups, componentList);

        int lastColumnIndex = 0;

        for (int i = 0; i < sortCount; i++) {
            int itemStartIndex = headRowIndex + i;
            Row itemRow = ExcelUtil.createRow(sheet, itemStartIndex);
            int approvalNumIndex = 1;                                       // 编号在第一列
            int applicantTimeLineIndex = 2;                                 // 申请时间在第二列

            // 编号
            ExcelUtil.createCommonCell(itemRow, approvalNumIndex, centerDataStyle, request.getApprovalNum());

            //  申请时间
            String createTime = changeCHTime(request.getCreateTime());
            ExcelUtil.createCommonCell(itemRow, applicantTimeLineIndex, centerDataStyle, createTime);
            sheet.setColumnWidth(applicantTimeLineIndex, 30 * 256);

            for (int num : numList) {
                for (TemplateComponent com : componentList) {
                    if (num != com.getNum()) {
                        continue;
                    }
                    int offset = numList.indexOf(num);
                    if (com.getType() == ContentType.COMPONENT_GROUP) {
                        offset = groupIndexMap.get(num);
                        logger.info("组件集子组件开始位置:{}", offset);

                        List<SortValue> sortValues = sortMap.get(num);
                        SortValue sortValue;
                        if (sortValues.size() <= i) {
                            sortValue = initEmptyValue(sortValues.get(0));
                        } else {
                            sortValue = sortValues.get(i);
                        }

                        List<Component> components = sortValue.getComponents();
                        for (int j = 0; j < components.size(); j++) {
                            int type = components.get(j).getType();
                            String value = components.get(j).getValue();
                            String name = components.get(j).getName();
                            logger.info("打印值:{}, 打印位置:{}, 列名:{}", value, offset + j, name);
                            if (type == MONEY_TYPE && StringUtils.isNotEmpty(value) && !"/".equals(value)) {
                                ExcelUtil.createCommonCell(itemRow, offset + j, centerDataStyle, Double.valueOf(value));
                            } else {
                                ExcelUtil.createCommonCell(itemRow, offset + j, centerDataStyle, value);
                            }
                        }
                    } else {
                        String value = getValue(offset, com, request);
                        value = removeNewLine(value);
                        int index = itemRow.getLastCellNum();  // startLine + offset
                        logger.info("普通组件打印位置:{}, 打印值[{}], 列名:{}", index, value, com.getName());
                        if (isLeftDataStyle(offset)) {
                            ExcelUtil.createCommonCell(itemRow, index, leftDataStyle, value);
                        } else {
                            if (typeList.get(offset) == MONEY_TYPE && StringUtils.isNotEmpty(value)) {   // 如果是金额项
                                if (ExcelUtil.isJsonArrayStr(value)) {
                                    ExcelUtil.createCommonCell(itemRow, index, centerDataStyle, value);
                                } else {
                                    ExcelUtil.createCommonCell(itemRow, index, centerDataStyle, Double.valueOf(value));
                                }
                            } else {
                                ExcelUtil.createCommonCell(itemRow, index, centerDataStyle, value);
                            }
                        }
                    }
                }
            }

            // 相关人群、审批数据
            printContent(request, itemRow, RequestFlag.RELA_PEOPLE);
            printContent(request, itemRow, RequestFlag.APPROVAL_STATUS);
            printContent(request, itemRow, RequestFlag.APPROVAL_STATUS);
            printContent(request, itemRow, RequestFlag.JUDGER);
            printContent(request, itemRow, RequestFlag.CC);
            printContent(request, itemRow, RequestFlag.CC_TEMP);
            printContent(request, itemRow, RequestFlag.ARCHIVER);
            printContent(request, itemRow, RequestFlag.CONFIRM_TIME);
            printContent(request, itemRow, RequestFlag.COMMENT);

            lastColumnIndex = itemRow.getLastCellNum();
        }

        try {
            if (sortCount > 1) {
                for (int i = 1; i < lastColumnIndex; i++) {
                    boolean isMerge = true;
                    for (LinkedList<Integer> groupColumnIndex : lineGroupIndexList) {
                        if (groupColumnIndex.contains(i)) {
                            logger.info("该{}不需要合并", i);
                            isMerge = false;
                            break;
                        }
                    }
                    if (isMerge) {
                        logger.info("开始行:{}, 结束行:{}, 开始列:{}, 结束列:{}", headRowIndex, headRowIndex + sortCount - 1, i, i);
                        ExcelUtil.createRegion(sheet, headRowIndex, headRowIndex + sortCount - 1, i, i);
                    }
                }
            }
        } catch (Throwable e) {
            logger.error(e.getMessage(), e);
        }

        return sortCount;
    }


    private void printSubComponent(List<TemplateComponent> comList, int startLineIndex, Row itemRow, List<ComponentGroup> groups) {
        lastOffset = 0;
        for (TemplateComponent component : comList) {
            int num = component.getNum();
            String name = component.getName();
            int type = component.getType();
            if (!numList.contains(num)) {           // 此组件无需打印
                continue;
            }
            int lineOffset = numList.indexOf(num);                          // 得到该组件在模板中的位置
            int lineIndex = startLineIndex + 2 + lineOffset;                // 开始列加一列（隔开编号、申请时间）再加上它的偏移量
            logger.info("子组件Num{} | name:{} | lineIndex:{}, lineOffSet:{}", num, name, lineIndex, lineOffset);

            if (type == ContentType.COMPONENT_GROUP) {
                for (ComponentGroup group : groups) {
                    if (num == group.getNum()) {

                        int leftIndex = lineIndex + lastOffset;
                        int rightIndex = leftIndex + group.getComponents().size();
                        lastOffset += group.getComponents().size() - 1;

                        logger.info("leftIndex:{}, rightIndex:{}, lastOffset:{}", leftIndex, rightIndex, lastOffset);
                        int j = 0;
                        for (int i = leftIndex; i < rightIndex; i++) {
                            Component com = group.getComponents().get(j++);
                            // 打印每个子组件集
                            ExcelUtil.createCommonCell(itemRow, i, headStyle, com.getName());
                        }
                    }
                }
            } else {
                ExcelUtil.createCommonCell(itemRow, lineIndex + lastOffset, headStyle, name);        // 创建该组件
            }
        }
    }


    // 已打印组件集数量
    private int lastOffset = 0;

    private void printGroupCom(List<TemplateComponent> comList, int startLineIndex, Row itemRow, List<ComponentGroup> groups) {
        // 已打印组件集中子组件的数量
        lastOffset = 0;
        for (TemplateComponent component : comList) {
            int num = component.getNum();
            String name = component.getName();
            int type = component.getType();
            if (!numList.contains(num)) {           // 此组件无需打印
                continue;
            }
            int lineOffset = numList.indexOf(num);                          // 得到该组件在模板中的位置
            int lineIndex = startLineIndex + 2 + lineOffset;                // 开始列加一列（隔开编号、申请时间）再加上它的偏移量

            logger.info("组件Num{} | name:{} | 所在列:{}, type:{}", num, name, lineIndex, type);

            // 如果是组件集,则有多少个子组件就打印多少列
            if (type == ContentType.COMPONENT_GROUP) {
                for (ComponentGroup group : groups) {
                    if (num == group.getNum()) {
                        // 记录每个组件集中子组件的开始列和结束列
                        LinkedList<Integer> groupColumnIndex = new LinkedList<>();

                        int leftIndex = lineIndex + lastOffset;
                        int rightIndex = leftIndex + group.getComponents().size();
                        lastOffset += group.getComponents().size() - 1;

                        logger.info("子组件开始列:{}, 子组件结束列:{}, 已打印组件集中子组件的数量:{}", leftIndex, rightIndex - 1, lastOffset);
                        for (int i = leftIndex; i < rightIndex; i++) {
                            ExcelUtil.createCommonCell(itemRow, i, headStyle, name);
                            groupColumnIndex.add(i);
                        }
                        lineGroupIndexList.add(groupColumnIndex);

                        groupIndexMap.put(num, leftIndex);
                    }
                }
            } else {
                ExcelUtil.createCommonCell(itemRow, lineIndex + lastOffset, headStyle, name);        // 创建该组件
            }
        }
    }


    private void autoSizeOfSheetCell(HSSFSheet sheet) {
        Map<Integer, String> map = new HashMap<>();
        int maxCellIndex = getMaxSizeInSheet(sheet, map);
        Row maxRow = sheet.getRow(maxCellIndex);
        for (int index = 2; index <= maxRow.getPhysicalNumberOfCells(); index++) {     // 忽略第一列时间格式的宽度,前面已设置(固定)
            if (map.get(index) == null) {
                continue;
            }
            int colWidth = Integer.parseInt(map.get(index).substring(0, map.get(index).length() - 2));
            sheet.setColumnWidth(index, Math.min(colWidth, 12300));    // 限制最大长度为12300,因为单个单元格的宽度不能超过255个字符,这里的12300是测试看起来顺眼的,没有其他的意义
        }
    }

    private int getMaxSizeInSheet(HSSFSheet sheet, Map<Integer, String> map) {       // sheet,最后一行
        int columnSize = 0;        // 保存列数
        int maxCellIndex = 0;    // 保存有最多列数的行num
        for (int i = 7; i <= sheet.getLastRowNum(); i++) {        // 行的计数从7开始,忽略掉表头
            Row row = sheet.getRow(i);    // 第i行
            if (row == null) {
                continue;
            }

            if (columnSize < row.getPhysicalNumberOfCells()) {
                columnSize = row.getPhysicalNumberOfCells();
                maxCellIndex = i;
            }

            for (int j = 1; j <= row.getPhysicalNumberOfCells(); j++) {    // 遍历该行的列数,列的计数从1开始,,这里从2计数是为了忽视掉：2017年7月 这样的表格头行
                Cell test = row.getCell(j);
                if (test == null) {
                    continue;
                }

                String value = null;
                if (test.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {     // 单元格类型为数值
                    value = test.getNumericCellValue() + "";
                } else if (test.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                    value = test.getStringCellValue().trim();
                }

                if (StringUtils.isEmpty(value)) {
                    continue;
                }

                double valueLength = getValueLength(value);

                /* 时间日期格式,设置为固定的 */
                valueLength = getDateValueLength(value, valueLength);

                if (map.containsKey(j)) {
                    if (Double.parseDouble(map.get(j)) < valueLength) {  // 本次的列长度大于map的保存的长度,则替换
                        map.put(j, Math.ceil(valueLength) + "");
                    }
                } else {
                    map.put(j, Math.ceil(valueLength) + "");
                }
            }
        }

        return maxCellIndex;
    }

    private double getValueLength(String value) {
        double tempLength = 0;
        if (value.contains("\n")) {                    // 用于辨别出最长的那条评论或关联客户,以它作为该单元格的宽度
            String[] lenArray = value.split("\n");
            int maxLen = 0;
            for (String s : lenArray) {
                if (s.getBytes().length > maxLen) {
                    maxLen = s.getBytes().length;
                }
            }
            tempLength = maxLen * 256;            // 330
        } else {
            tempLength = value.getBytes().length * 300;
        }

        return tempLength;
    }

    private double getDateValueLength(String value, double valueLength) {
        double tempLength = valueLength;

        if (value.length() == 19) {
            String tempValue = value.substring(0, 10);       // 2017-09-22 周六 22:04 --> 2017-09-22
            if (ValidateUtil.isValidDate(tempValue)) {
                tempLength = 7726.0;
            }
        } else if (ValidateUtil.isValidDate(value)) {     // 2017-09-22
            tempLength = 4120;
        } else if (ValidateUtil.isValidDateTime(value)) { // 2017-09-22 22:04
            tempLength = 6557;
        }

        return tempLength;
    }


    // 打印模板组件
    private void createComponentInSheet(List<TemplateComponent> comList, int startLineIndex, Row itemRow) {
        for (TemplateComponent component : comList) {
            int num = component.getNum();
            if (!numList.contains(num)) {           // 此组件无需打印
                continue;
            }
            String name = component.getName();
            int lineOffset = numList.indexOf(num);                          // 得到该组件在模板中的位置
            int lineIndex = startLineIndex + 2 + lineOffset;              // 开始列加2列（隔开编号、申请时间）再加上它的偏移量
            ExcelUtil.createCommonCell(itemRow, lineIndex, headStyle, name);        // 创建该组件
        }
    }


    private void shiftTemplateComponents(List<TemplateComponent> comList) {
        numList = new ArrayList<>();                                //    组件的num: 决定位置
        typeList = new ArrayList<>();                            //    组件的type: 类型
        for (TemplateComponent temp : comList) {
            if (temp.getType() > 7 && temp.getType() < 12) {    //模板组件中： 8：相关任务，9：相关外出，10：相关申购，11：相关附件,这些信息要排除出去
                continue;
            } else if (temp.getType() == 16) {                // 把这个 [新增一条打卡 ]去掉
                continue;
            } else if (temp.getType() == 6 && temp.getNeed() == 2 && temp.getNum() == 5) {       // 把[新增一个目的地]去掉
                continue;
            }
            logger.info("组件位置:" + temp.getNum() + " 组件类型:" + temp.getType() + " 组件名字:" + temp.getName());
            numList.add(temp.getNum());
            typeList.add(temp.getType());
        }
    }

    // 该公司<contactId, people>的Map
    private Map<Integer, People> contactMap;

    private Map<Integer, People> convertUserToPeople(List<User> userList) {
        Map<Integer, People> contactMap = new HashMap<>();
        for (User user : userList) {
            People people = new People();
            people.initPeople();
            people.setContactId(user.getContactId());
            people.setName(user.getContactName());
            if (user.getQuit()) {
                people.setDeleteTime("已离职");   // 随意设置一个离职标记,用于方法判断给员工离职
            }
            contactMap.put(user.getContactId(), people);
        }
        return contactMap;
    }


    // 导出范围: 2018年3月10日-2018年3月31日(共21日)
    private String getExportScopeStrByDays(List<String> days) throws Throwable {
        String exportScopeStr = "";
        exportScopeStr += changeChDay(days.get(0));
        if (days.size() > 1) {
            exportScopeStr += "-" + changeChDay(days.get(days.size() - 1));
            exportScopeStr += "（共" + countDays(days.get(0), days.get(1)) + "日）";
        }
        return exportScopeStr;
    }

    // 计算时间:天数
    private long countDays(String startDay, String endDay) throws Throwable {
        long startTime = ymdSDF.parse(startDay).getTime();
        long endTime = ymdSDF.parse(endDay).getTime();
        return (endTime - startTime) / (1000 * 60 * 60 * 24) + 1;
    }

    // yyyy-MM-dd --> yyyy年M月d日
    private String changeChDay(String day) throws ParseException {
        return ymdCHSDF.format(ymdSDF.parse(day).getTime());
    }


    //		公司名 ：西木由视众传媒与文化有限公司
//		审批类型 ：高级外出、请假、灵活审批流测试
//		导出范围 ：2018年3月10日-2018年3月31日(共21日)
//		导出时间 ：2017-08-26 11:25
//		导出人 ：洛夕佑
    private String getEmailContentByDays(String templateName) throws Throwable {
        User user = excelParam.getUser();
        List<String> days = excelParam.getDays();
        return "" + "公司名 :" + user.getCompanyName() + "<br>"
                + "审批类型 :" + templateName + "<br>"
                + "导出范围 :" + getExportScopeStrByDays(days) + "<br>"
                + "导出时间 :" + hmSDF.format(System.currentTimeMillis()) + "<br>"
                + "导出人 :" + user.getContactName();
    }


    // 设置无网格线
    private void setNoGridLines(HSSFSheet sheet) {
        sheet.setDisplayGridlines(false);       // 无网格线
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.getPrintSetup().setLandscape(false);
    }

    /**
     * 发送邮件
     * 参数:
     * emails --- 邮箱
     * emailContent:文本内容
     * emailFileList: 附件
     */
    private void sendEmail(String emailContent, List<File> emailFileList) throws Throwable {
        List<String> emails = excelParam.getEmails();
        String emailTitle = "大管加审批记录导出";
        for (String email : emails) {
            ExcelUtil.sendQQMail(email, emailTitle, emailContent, emailFileList);
        }
    }

    // 保存一份文件至服务器
    private List<File> getFileList(HSSFWorkbook wb, StringBuilder templateNames) throws Throwable {
        User user = excelParam.getUser();
        String exportPath = excelParam.getExportPath();

        File export = new File(exportPath);
        if (!export.exists()) {
            export.mkdir();
        }

        String[] names = templateNames.toString().split("、");
        StringBuilder fileName = new StringBuilder();
        for (int i = 0; i < names.length; i++) {
            fileName.append(names[i]).append("、");
            if (i == 2) {                               // 超出三个,取三个模板名即可
                fileName.deleteCharAt(fileName.length() - 1);
                fileName.append("...");
                break;
            }
        }
        if (fileName.toString().endsWith("、")) {
            fileName.deleteCharAt(fileName.length() - 1);
        }
        logger.info("文件名: " + fileName);
        String name = CheckUtil.replaceNonlicetFilePathCharacter(fileName.toString());
        String filePath = exportPath + "/" + user.getCompanyName() + "-" + name + "_" + UUID.randomUUID() + ".xls";
        File file = new File(filePath);
        if (!file.exists()) {
            file.createNewFile();
        }
        wb.write(file);
        wb.close();
        List<File> fileList = new ArrayList<>();
        fileList.add(file);
        return fileList;
    }

    // 合并标题单元格
    // 参数:lastHeaderRowIndex --> 第一个模板标题所在行
    private void mergeHeaderRegion(HSSFSheet sheet, int lastHeaderRowIndex) {
        int lastColIndex = sheet.getRow(lastHeaderRowIndex + 2).getLastCellNum() - 1;
        for (int rowIndex = 1; rowIndex <= lastHeaderRowIndex; rowIndex++) {
            int topBorder = 0;
            int downBorder = 0;
            if (rowIndex == 1) {
                topBorder = 1;
                downBorder = 0;
            } else if (rowIndex == lastHeaderRowIndex) {
                downBorder = 1;
            }
            ExcelUtil.createRegion(sheet, rowIndex, rowIndex, 1, lastColIndex, topBorder, downBorder, 1, 1);
        }
    }

    // 获取每个模板的组件
    private List<TemplateComponent> getComponentList(Template template) {
        List<TemplateComponent> comList = JSONObject.parseArray(template.getTemplateComponent(), TemplateComponent.class);
        comList.sort(comparingInt(TemplateComponent::getNum));
        return comList;
    }

    /**
     * 新版报销版本
     */
    private static HashMap<Integer, Request> newExpenseVersionMap = new HashMap();


    private void printRequestContent(HSSFSheet sheet, List<Request> requestList, int headRowIndex) throws Throwable {
        for (int i = 0; i < requestList.size(); i++) {
            Request request = requestList.get(i);

            // 新版本报销的审批导出
            if (newExpenseVersionMap.containsKey(request.getRequestDkey())) {
                headRowIndex += printNewExpenseContent(sheet, request, headRowIndex);
                continue;
            }

            int itemStartIndex = headRowIndex + 1 + i;       // 标题行下一行,即内容开始行
            int approvalNumIndex = 1;                        // 编号在第一列
            int applicantTimeLineIndex = 2;                  // 申请时间在第2列
            int startLine = 3;                               // 内容开始列
            Row itemRow = ExcelUtil.createRow(sheet, itemStartIndex);

            // 编号
            ExcelUtil.createCommonCell(itemRow, approvalNumIndex, centerDataStyle, request.getApprovalNum());

            //  申请时间
            String createTime = changeCHTime(request.getCreateTime());
            ExcelUtil.createCommonCell(itemRow, applicantTimeLineIndex, centerDataStyle, createTime);
            sheet.setColumnWidth(applicantTimeLineIndex, 30 * 256);

            // 模板组件中对应的审批内容
            List<TemplateComponent> reqComponents = JSONObject.parseArray(request.getTemplateComponent(), TemplateComponent.class);
            for (int num : numList) {
                for (TemplateComponent reqCom : reqComponents) {
                    if (num != reqCom.getNum()) {
                        continue;
                    }
                    int offset = numList.indexOf(num);
                    // 获取组件的值
                    String value = getValue(offset, reqCom, request);
                    value = removeNewLine(value);
                    if (isLeftDataStyle(offset)) {
                        ExcelUtil.createCommonCell(itemRow, startLine + offset, leftDataStyle, value);
                    } else {
                        // 如果是金额项
                        if (typeList.get(offset) == MONEY_TYPE && StringUtils.isNotEmpty(value)) {
                            if (ExcelUtil.isJsonArrayStr(value)) {
                                ExcelUtil.createCommonCell(itemRow, startLine + offset, centerDataStyle, value);
                            } else {
                                ExcelUtil.createCommonCell(itemRow, startLine + offset, centerDataStyle, Double.valueOf(value));
                            }
                        } else {
                            ExcelUtil.createCommonCell(itemRow, startLine + offset, centerDataStyle, value);
                        }
                    }
                }
            }

            if (request.getDefaultType() == Template.LEAVE) {
                printContent(request, itemRow, RequestFlag.LEAVE_DURATION);
            }
            // 相关人群、审批数据
            printContent(request, itemRow, RequestFlag.RELA_PEOPLE);
            printContent(request, itemRow, RequestFlag.APPROVAL_STATUS);
            printContent(request, itemRow, RequestFlag.JUDGER);
            printContent(request, itemRow, RequestFlag.CC);
            printContent(request, itemRow, RequestFlag.CC_TEMP);
            printContent(request, itemRow, RequestFlag.ARCHIVER);
            printContent(request, itemRow, RequestFlag.CONFIRM_TIME);
            printContent(request, itemRow, RequestFlag.COMMENT);

        }
    }

    /**
     * 新版报销的重复打印,然后合并除了 报销事项、报销金额、报销支出以外的列
     *
     * @param sheet
     * @param req
     * @param headRowIndex
     * @return
     * @throws Throwable
     */
    private int printNewExpenseContent(HSSFSheet sheet, Request req, int headRowIndex) throws Throwable {
        List<TemplateComponent> reqComponents = JSONObject.parseArray(req.getTemplateComponent(), TemplateComponent.class);
        int valueCount = 0;         // 需要重复打印多少次
        for (int num : numList) {
            for (TemplateComponent reqCom : reqComponents) {
                if (num == 1 || num == 2 || num == 3) {
                    JSONArray arr = JSONArray.parseArray(reqCom.getValue());
                    valueCount = arr.size();
                    break;
                }
            }
        }

        // 合并开始行、结束行
        int mergeStartLine = headRowIndex + 1;
        int mergeEndLine = headRowIndex + valueCount;

        for (int i = 0; i < valueCount; i++) {
            int itemStartIndex = headRowIndex + 1 + i;
            Row itemRow = ExcelUtil.createRow(sheet, itemStartIndex);
            int approvalNumIndex = 1;                                       // 编号在第一列
            int applicantTimeLineIndex = 2;                                 // 申请时间在第二列
            int startLine = 3;                                              // 组件内容第三列

            // 编号
            ExcelUtil.createCommonCell(itemRow, approvalNumIndex, centerDataStyle, req.getApprovalNum());

            //  申请时间
            String createTime = changeCHTime(req.getCreateTime());
            ExcelUtil.createCommonCell(itemRow, applicantTimeLineIndex, centerDataStyle, createTime);
            sheet.setColumnWidth(applicantTimeLineIndex, 30 * 256);

            for (int num : numList) {
                for (TemplateComponent reqCom : reqComponents) {
                    if (num != reqCom.getNum()) {
                        continue;
                    }

                    int offset = numList.indexOf(num);
                    String value = getValue(offset, reqCom, req);       // 获取组件的值
                    value = removeNewLine(value);

                    if (isLeftDataStyle(offset)) {
                        ExcelUtil.createCommonCell(itemRow, startLine + offset, leftDataStyle, value);

                    } else {

                        if (num == 1 || num == 2 || num == 3) {         // 报销事项、金额、支出日期
                            JSONArray arr = JSON.parseArray(value);
                            value = arr.getString(i);
                        }

                        if (typeList.get(offset) == MONEY_TYPE && StringUtils.isNotEmpty(value)) {   // 如果是金额项
                            ExcelUtil.createCommonCell(itemRow, startLine + offset, centerDataStyle, Double.valueOf(value));

                        } else {
                            ExcelUtil.createCommonCell(itemRow, startLine + offset, centerDataStyle, value);
                        }
                    }
                }
            }


            // 相关人群、审批数据
            printContent(req, itemRow, RequestFlag.RELA_PEOPLE);
            printContent(req, itemRow, RequestFlag.APPROVAL_STATUS);
            printContent(req, itemRow, RequestFlag.JUDGER);
            printContent(req, itemRow, RequestFlag.CC);
            printContent(req, itemRow, RequestFlag.CC_TEMP);
            printContent(req, itemRow, RequestFlag.ARCHIVER);
            printContent(req, itemRow, RequestFlag.CONFIRM_TIME);
            printContent(req, itemRow, RequestFlag.COMMENT);
        }

        // 合并重复的行,报销事项、金额、支出日期无需合并
        if (valueCount > 1) {
            Row itemRow = sheet.getRow(sheet.getLastRowNum());
            for (int j = 1; j <= itemRow.getPhysicalNumberOfCells(); j++) {
                // 报销事项、金额、支出日期所在列
                if (j >= 3 && j <= 5) {
                    continue;
                }
                ExcelUtil.createRegion(sheet, mergeStartLine, mergeEndLine, j, j, 1, 1, 1, 1);
            }
        }

        return valueCount;
    }


    // 消除内容最前面的换行和最后面的换行
    private String removeNewLine(String value) {
        String tempValue = value;
        if (StringUtils.isEmpty(tempValue)) {
            return value;
        }

        while (tempValue.startsWith("\n")) {
            tempValue = tempValue.substring(1);
        }

        while (tempValue.endsWith("\n")) {
            tempValue = tempValue.substring(0, tempValue.length() - 1);
        }

        return tempValue;
    }

    // 14为关联客户,2为多行文本,6为外出地点
    private boolean isLeftDataStyle(int offset) {
        boolean flag = false;
        switch (typeList.get(offset)) {
            case CLIENT_TYPE:
            case MORE_TEXT:
            case SITE_TYPE:
                flag = true;
                break;
            default:
                break;
        }
        return flag;
    }

    private static final int MORE_TEXT = 2;
    private static final int TIME_TYPE = 5;
    private static final int SITE_TYPE = 6;
    private static final int MORE_SELECT = 12;
    private static final int MONEY_TYPE = 13;
    private static final int CLIENT_TYPE = 14;
    private static final int ATTENDANCE_TYPE = 15;
    /**
     * 补卡班次
     */
    private static final int RECEIVE_CLOCK_TICK = 21;
    /**
     * 补卡类型 0.签到;1.签退;2.上午签到;3.上午签退;4.下午签到;5.下午签退
     */
    private static final int RECEIVE_CLOCK_TYPE = 22;
    /**
     * 补卡时段
     */
    private static final int RECEIVE_CLOCK_TIME = 23;

    private String getValue(int offset, TemplateComponent reqCom, Request req) throws Throwable {
        StringBuilder value = new StringBuilder();
        String comValue = reqCom.getValue();
        if (StringUtils.isEmpty(comValue) && typeList.get(offset) != CLIENT_TYPE) {
            return value.toString();
        }

        switch (typeList.get(offset)) {
            case TIME_TYPE:
                value.append(changeCHTime(comValue));
                break;

            case SITE_TYPE:
                value.append(getSiteTypeValue(comValue));
                break;

            case MORE_SELECT:
                value.append(getMoreSelectValue(comValue));
                break;

            case CLIENT_TYPE:

            case ContentType.RELATE_SUPPLIER:
                value.append(getClientTypeValue(req.getRequestAncestorId()));
                break;

            case ATTENDANCE_TYPE:          // 如果是考勤方案,则截取最后一个数据字段,eg:美国考勤,2710; 截取为:美国考勤
                if (comValue.contains(",")) {
                    value.append(comValue, 0, comValue.lastIndexOf(","));
                } else {
                    value.append(comValue);
                }
                break;

            case RECEIVE_CLOCK_TICK:
                value.append(getClockTick(comValue));
                break;

            case RECEIVE_CLOCK_TYPE:
                value.append(getClockTypeValue(comValue));
                break;

            case RECEIVE_CLOCK_TIME:
                value.append(getClockTime(comValue));
                break;

            case ContentType.DAYS_OFF_TOTAL_TIME:
                DaysOffTimeInfo daysOffTimeInfo = JSONObject.parseObject(comValue, DaysOffTimeInfo.class);
                value.append(daysOffTimeInfo.getTotalTime());
                break;

            default:
                value.append((comValue == null ? " " : comValue));
                break;
        }
        return value.toString();
    }


    private String getClockTime(String comValue) {
        JSONObject jsonObject = JSON.parseObject(comValue);
        return jsonObject.getString("scheduleWorkTime");
    }


    private String getClockTick(String comValue) {
        JSONObject jsonObject = JSON.parseObject(comValue);
        return jsonObject.getString("scheduleName");
    }


    private String getClockTypeValue(String comValue) {
        String value = "";
        switch (comValue) {
            case "0":
                value = "签到";
                break;
            case "1":
                value = "签退";
                break;
            case "2":
                value = "上午签到";
                break;
            case "3":
                value = "上午签退";
                break;
            case "4":
                value = "下午签到";
                break;
            case "5":
                value = "下午签退";
                break;
            default:
                break;
        }

        return value;
    }

    private static final String CRM_CUSTOMER_URL = "https://www.erplus.co/api/v1/crm/customers/";
    private static final String URL_SUFFIX = "/customerInfo";

    //如果为关联客户,先调用德伟接口获取客户id,再写一个dao获取客户名
    private String getClientTypeValue(int ancestorId) throws Exception {
        StringBuilder value = new StringBuilder();
        List<Customer> customerList = getRelateCustomer(ancestorId);    //获取关联客户id
        if (ListHelper.isNullEmpty(customerList)) {
            return value.toString();
        }

        String customerIds = getCustomerIds(customerList);
        logger.info("url:{}", CRM_CUSTOMER_URL + customerIds + URL_SUFFIX);
        String result = HTTPUtils.get(CRM_CUSTOMER_URL + customerIds + URL_SUFFIX, excelParam.getAtoken());
        logger.info("获取信息:{}", result);

        setCustomerNames(customerList, result);

        for (int a = 0; a < customerList.size(); a++) {
            Customer cus = customerList.get(a);
            value.append((cus.getName() == null ? "" : cus.getName()));
            if (customerList.size() - a > 1 && cus.getName() != null) {
                value.append("\n");
            }
        }
        return value.toString();
    }

    private void setCustomerNames(List<Customer> customerList, String result) {
        if (StringUtils.isEmpty(result)) {
            return;
        }
        JSONObject jsonObject = JSON.parseObject(result);
        JSONArray arr = jsonObject.getJSONArray("item");
        for (int i = 0; i < arr.size(); i++) {
            JSONObject object = arr.getJSONObject(i);
            String name = object.getString("name");
            String id = object.getString("id");

            logger.info("id:{}, name:{}", id, name);
            for (Customer customer : customerList) {
                if (Objects.equals(id, customer.getObjectIdB())) {
                    customer.setName(name);
                    break;
                }
            }
        }
    }

    private String getCustomerIds(List<Customer> customerList) {
        List<String> customerIds = new ArrayList<>(customerList.size());

        for (Customer customer : customerList) {
            customerIds.add(customer.getObjectIdB());
        }

        return ListHelper.list2string(customerIds);
    }


    private String getMoreSelectValue(String comValue) {
        return comValue.replaceAll("\\[", "").replaceAll("]", "").replaceAll("\"", "");
    }

    private String getSiteTypeValue(String comValue) {
        StringBuilder value = new StringBuilder();
        String temp = comValue.replace("[", "").replace("]", "").replaceAll("\"", "");
        if (temp.contains(",")) {
            String[] outValue = temp.split(",");
            for (int j = 0; j < outValue.length; j++) {
                value.append("目的地").append(j + 1).append(" : ").append(outValue[j]);
                if (outValue.length - j > 1) {
                    value.append("\n");
                }
            }
        } else {
            value.append("目的地 : ").append(temp);
        }
        return value.toString();
    }

    private List<Customer> getRelateCustomer(int objectIdB) throws Exception {
        int type = 1;
        String param = "objectId=" + objectIdB + "&type=" + type;
        String url = "https://www.erplus.co/taskWeb/getProjectRelate" + "?" + param;
        String result = HTTPUtils.get(url, excelParam.getAtoken());
        logger.info("获取客户url:" + url + param);
        logger.info("获取后的数据：" + result);

        JSONObject rejo = JSONObject.parseObject(result);
        List<Customer> customerList = JSONObject.parseArray(rejo.getString("item"), Customer.class);
        if (ListHelper.isNullEmpty(customerList)) {
            return customerList;
        }
        List<Customer> list = new ArrayList<>();
        for (Customer customer : customerList) {
            // 2-客户;11-供应商
            if (customer.getTypeB() == 2 || customer.getTypeB() == 11) {
                list.add(customer);
            }
        }
        return list;
    }


    private void printContent(Request req, Row row, RequestFlag flag) throws Throwable {
        int index = getCellIndexByFlag(flag);
        CellStyle style = getCellStyle(flag);
        String reqContent = getPrintContent(req, flag);
        if (reqContent == null) {
            ExcelUtil.createCommonCell(row, index, style, "-");
        } else {
            if (isPerson(flag)) {
                reqContent = getContactsName(reqContent);
            } else if (flag == RequestFlag.CONFIRM_TIME) {
                reqContent = changeCHTimeV2(reqContent);
            }
            ExcelUtil.createCommonCell(row, index, style, reqContent);
        }
    }

    private String getPrintContent(Request request, RequestFlag flag) throws Throwable {
        String content = "";
        switch (flag) {
            case JUDGER:
                content = request.getAllJudger();
                break;
            case CC:
                content = request.getCc();
                break;
            case CC_TEMP:
                content = request.getCcTemp();
                break;
            case RELA_PEOPLE:
                content = request.getRelaPeople();
                break;
            case APPROVAL_STATUS:
                switch (request.getFinished()) {
                    case Finished.AGREED:
                        content = "已批准";
                        break;
                    case Finished.REFUSED:
                        content = "已驳回";
                        break;
                    case Finished.CALLING_BACK:
                        content = "已撤销";
                        break;
                    case Finished.CANCELLED:
                        content = "已作废";
                        break;
                    case Finished.ARCHIVED:
                        content = "已归档";
                        break;
                    case Finished.APPROVING:
                        content = "待批准";
                        break;
                    default:
                        content = "";
                        break;
                }
                break;
            case ARCHIVER:
                content = request.getArchived();
                break;
            case CONFIRM_TIME:
                content = request.getLatestApprovedTime();
                break;
            case COMMENT:
                content = getComment(request);
                break;
            case LEAVE_DURATION:
                content = request.getNaturalContentTimeLast();
                break;
            default:
                break;
        }
        return content;
    }

    private boolean isPerson(RequestFlag flag) {
        boolean mark = false;
        switch (flag) {
            case CONFIRM_TIME:
            case COMMENT:
            case LEAVE_DURATION:
            case APPROVAL_STATUS:
                break;
            default:
                mark = true;   // 除批准时间、审批评论
        }
        return mark;
    }

    private CellStyle getCellStyle(RequestFlag flag) {
        CellStyle style;
        if (flag == RequestFlag.COMMENT) {
            style = leftDataStyle;
            // 审批评论左对齐
        } else {
            style = centerDataStyle;
        }
        return style;
    }


    private int relatePeopleIndex;
    private int statusIndex;
    private int approverColIndex;
    private int ccColIndex;
    private int ccTempColIndex;
    private int archiveIndex;
    private int approvalTimeIndex;
    private int commentIndex;
    private int leaveDurationIndex;

    private int getCellIndexByFlag(RequestFlag flag) {
        int index = 0;
        switch (flag) {
            case JUDGER:
                index = approverColIndex;
                break;
            case CC:
                index = ccColIndex;
                break;
            case CC_TEMP:
                index = ccTempColIndex;
                break;
            case RELA_PEOPLE:
                index = relatePeopleIndex;
                break;
            case APPROVAL_STATUS:
                index = statusIndex;
                break;
            case ARCHIVER:
                index = archiveIndex;
                break;
            case CONFIRM_TIME:
                index = approvalTimeIndex;
                break;
            case COMMENT:
                index = commentIndex;
                break;
            case LEAVE_DURATION:
                index = leaveDurationIndex;
                break;
            default:
                break;
        }
        return index;
    }

    enum RequestFlag {
        JUDGER, CC, CC_TEMP, RELA_PEOPLE, ARCHIVER, CONFIRM_TIME, COMMENT, LEAVE_DURATION, APPROVAL_STATUS
    }

    // 获取审批评论
    private String getComment(Request req) throws Throwable {
        ReadTemplateServicePrx rts = excelParam.getReadService();
        User user = excelParam.getUser();
        StringBuilder sb = new StringBuilder();
        // 获取评论
        List<Comment> list = rts.getCommentMessage(req, user);
        if (!ListHelper.isNullEmpty(list)) {
            for (int j = 0; j < list.size(); j++) {
                Comment com = list.get(j);
                sb.append(com.getCrtName()).append(":").append(com.getMsg());
                if (list.size() - j > 1) {
                    sb.append("\n");
                }
            }
        }
        return sb.toString();
    }


    private String getContactsName(String idsStr) {
        if (idsStr == null || "".equals(idsStr)) {
            return null;
        }
        StringBuilder result = new StringBuilder();
        for (String idStr : idsStr.split(",")) {
            int id = Integer.parseInt(idStr);
            result.append(getContactName(contactMap.get(id))).append(",");
        }
        if (result.length() > 0) {
            result.deleteCharAt(result.length() - 1);
        }
        return result.toString();
    }

    private String getContactName(People people) {
        if (people == null || people.getName() == null) {
            return "查无此人";
        }
        if (people.getDeleteTime() != null) {
            return people.getName() + "（已离职）";
        }
        return people.getName();
    }

    private String changeCHTime(String fromTime) throws ParseException {
        if (StringUtils.isEmpty(fromTime)) {
            return "";
        }
        String toTime = chineseHMSDF.format(hmSDF.parse(fromTime));
        return toTime.replaceAll("星期", "周").replaceAll("天", "日");
    }

    private String changeCHTimeV2(String fromTime) {
        if (StringUtils.isEmpty(fromTime)) {
            return "";
        }
        String toTime = chineseHMSDF.format(DateTimeHelper.parse(fromTime));
        return toTime.replaceAll("星期", "周").replaceAll("天", "日");
    }
}
